-- @owner: lwx1120110
-- @date: 2023-02-10
-- @testpoint: 结合事务验证invisible属性

--step1:设置参数;expect:设置成功
set dolphin.optimizer_switch = 'use_invisible_indexes=off';

--step2:创建表;expect:创建成功
drop table if exists t_invisible_0024;
create table t_invisible_0024
(col1 int,col2 text,col3 text);

--step3:结合事务创建invisible索引;expect:创建成功
begin;
drop index if exists idx_invisible_0024;
create index idx_invisible_0024 on t_invisible_0024(col1) invisible;
end;
/

--step4:插入数据;expect:插入成功
insert into t_invisible_0024 values(
generate_series(1,100000),'test'||(round(random()*100,2)),round(random()*100,2));

--step5:explain查询计划;expect:不执行该索引
explain(costs false) select col1 from t_invisible_0024 where col1>=8321 and col3<45;

--step6:ALTER INDEX设置invisible结合事务;expect:设置成功
begin;
alter table t_invisible_0024 alter index idx_invisible_0024 visible;
end;
/

--step7:explain查询计划;expect:执行该索引
explain(costs false) select col1 from t_invisible_0024 where col1>=44271 and col3>58;

--step8:清理环境;expect:成功
drop index if exists idx_invisible_0024;
drop table if exists t_invisible_0024 cascade constraints;
reset dolphin.optimizer_switch;